package com.jy.dao;

import com.jy.bean.Lndividual_orders;
import com.jy.bean.Order;
import com.jy.bean.Order_browse;
import com.jy.bean.Orderdetail;
import com.jy.util.DBUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

public class OrderDao {
    private static final Logger log = LoggerFactory.getLogger(UserDao.class);

    /**
     * 查询出所有的订单信息并算出该订单的总金额
     * @return
     */
    public List<Orderdetail> consigneeList(){
        List<Orderdetail> orderlies = new ArrayList<>();
        List<Orderdetail> orderdetailList_1 = OrderDao.consigneeByorder();
        List<Orderdetail> orderdetailList_2 = OrderDao.Orderdetailprice();
        for (int i = 0; i < orderdetailList_1.size(); i++) {
            Orderdetail orderdetail = new Orderdetail();
            orderdetail.setOrdernumber(orderdetailList_1.get(i).getOrdernumber());
            orderdetail.setOrderSubmissionTime(orderdetailList_1.get(i).getOrderSubmissionTime());
            orderdetail.setState(orderdetailList_1.get(i).getState());
            orderdetail.setReceiverAddress(orderdetailList_1.get(i).getReceiverAddress());
            orderdetail.setAggregate(orderdetailList_2.get(i).getAggregate());

            log.debug("count  :"+orderdetail);
            orderlies.add(orderdetail);
        }

        return orderlies;
    }

    /**
     * 查询所有订单的总价
     * @param
     * @return
     */
    public static List<Orderdetail> Orderdetailprice(){
        List<Orderdetail> orderdetails = new ArrayList<>();
        String sql = "SELECT SUM(b.payable*b.quantity) FROM m_order a INNER JOIN m_orderdetail b ON a.ordernumber=b.ordernumber GROUP BY a.ordernumber";
        log.debug(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Orderdetail orderdetail = new Orderdetail();
                orderdetail.setAggregate(rs.getInt(1));
                orderdetails.add(orderdetail);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return orderdetails;
    }

    /**
     * 查询所有订单的订单号、订单时间、订单状态、地址
     * @return
     */
    public static List<Orderdetail> consigneeByorder(){
        List<Orderdetail> orderdetails = new ArrayList<>();
        String sql = "SELECT ordernumber,OrderSubmissionTime,state,receiverAddress FROM m_order WHERE ordernumber in (SELECT b.ordernumber from m_orderdetail as b GROUP BY b.ordernumber);";
        log.debug(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                Orderdetail orderdetail = new Orderdetail();
                orderdetail.setOrdernumber(rs.getString(1));
                orderdetail.setOrderSubmissionTime(rs.getString(2));
                orderdetail.setState(rs.getString(3));
                orderdetail.setReceiverAddress(rs.getString(4));
                log.debug(String.valueOf(orderdetail));
                orderdetails.add(orderdetail);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return orderdetails;
    }

    /**
     * 向订单表添加一条订单
     * @param order
     * @return
     */
    public Boolean insertOrder(Order order){
        Boolean favg = false;
        String sql = "insert INTO m_order (user_id,OrderSubmissionTime,ordernumber,state,timelimit,receiverAddress,consigneeName,phone) values (?,?,?,?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,order.getUser_id());
            ps.setString(2, nuw());
            ps.setString(3,order.getOrderdnumber());
            ps.setString(4,"未付款");
            ps.setString(5,dataAdd());
            ps.setString(6,order.getReceiverAddress());
            ps.setString(7,order.getConsigneeName());
            ps.setString(8,order.getPhone());
            log.debug("时间对比："+nuw()+"----"+dataAdd());
            favg = ps.executeUpdate()>0;
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return favg;
    }

    /**
     * 向订单详细表添加一条订单
     * @param order
     * @return
     */
    public Boolean insertorderdetail(Order order){
        Boolean favg = false;
        String sql = "insert INTO m_orderdetail (ordernumber,user_id,consignee_id,payable,quantity) values (?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, order.getOrderdnumber());
            ps.setInt(2,order.getUser_id());
            ps.setInt(3,order.getConsignee_id());
            ps.setInt(4,order.getPayable());
            ps.setInt(5,order.getQuantity());
            favg = ps.executeUpdate()>0;
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return favg;
    }

    /**
     * 查询当前订单号的订单信息
     * @return
     */
    public List<Order> selectByOrderdnumber(String orderdnumber){
        List<Order> orders = new ArrayList<>();
        String sql = "SELECT * FROM m_order where orderdnumber=?";
        log.debug(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,orderdnumber);
            rs = ps.executeQuery();
            while (rs.next()){
                Order order = new Order();
                order.setOrder_id(rs.getInt(1));
                order.setUser_id(rs.getInt(2));
                order.setConsignee_id(rs.getInt(3));
                order.setPayable(rs.getInt(4));
                order.setQuantity(rs.getInt(5));
                order.setOrderSubmissionTime(rs.getString(6));
                order.setOrderdnumber(rs.getString(7));
                order.setState(rs.getString(8));
                order.setTimelimit(rs.getString(9));
                System.out.println(order);
                orders.add(order);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return orders;
    }

    /**
     * 查询订单今日流水
     * @return
     */
    public Integer selectturnover(){
        Integer turnover = 0;
        String sql = "SELECT SUM(payable*quantity) FROM m_orderdetail WHERE ordernumber in (SELECT ordernumber FROM m_order WHERE DATE_FORMAT(OrderSubmissionTime,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') AND state != '未付款');";
        log.debug(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                turnover += rs.getInt(1);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return turnover;
    }

    /**
     * 根据当前订单号来修改订单的默认状态
     * 以支付未发货
     * @param ordernumber
     * @return
     */
    public boolean UpdateOrderState(String ordernumber){
        boolean favgs = false;
        String sql = "UPDATE m_order SET state = ? WHERE ordernumber = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,"以支付未发货");
            ps.setString(2,ordernumber);
            favgs = ps.executeUpdate() > 0;
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return favgs;
    }

    /**
     * 根据当前订单号来修改订单的状态
     * @param ordernumber
     * @return
     */
    public boolean MerchantUpdateState(String ordernumber,String state){
        boolean favgs = false;
        String sql = "UPDATE m_order SET state = ? WHERE ordernumber = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,state);
            ps.setString(2,ordernumber);
            favgs = ps.executeUpdate() > 0;
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return favgs;
    }

    /**
     * 根据用户id查询用户的订单信息并算出该订单的总金额
     * @return
     */
    public List<Orderdetail> consigneeByIdList(Integer user_id){
        List<Orderdetail> orderlies = new ArrayList<>();
        List<Orderdetail> orderdetailList_1 = consigneeByIDorder(user_id);
        List<Orderdetail> orderdetailList_2 = OrderdetailByIdprice(user_id);
        for (int i = 0; i < orderdetailList_1.size(); i++) {
            Orderdetail orderdetail = new Orderdetail();
            orderdetail.setOrdernumber(orderdetailList_1.get(i).getOrdernumber());
            orderdetail.setOrderSubmissionTime(orderdetailList_1.get(i).getOrderSubmissionTime());
            orderdetail.setState(orderdetailList_1.get(i).getState());
            orderdetail.setReceiverAddress(orderdetailList_1.get(i).getReceiverAddress());
            orderdetail.setAggregate(orderdetailList_2.get(i).getAggregate());
            log.debug("count  :"+orderdetail);
            orderlies.add(orderdetail);
        }

        return orderlies;
    }

    /**
     * 根据用户id查询用户所有订单的总价
     * @param
     * @return
     */
    public static List<Orderdetail> OrderdetailByIdprice(Integer user_id){
        List<Orderdetail> orderdetails = new ArrayList<>();
        String sql = "SELECT SUM(b.payable*b.quantity) FROM m_order a INNER JOIN m_orderdetail b ON a.ordernumber=b.ordernumber GROUP BY a.ordernumber HAVING a.ordernumber in (SELECT ordernumber from m_order WHERE user_id = ?)";
        log.debug(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,user_id);
            rs = ps.executeQuery();
            while (rs.next()){
                Orderdetail orderdetail = new Orderdetail();
                orderdetail.setAggregate(rs.getInt(1));
                orderdetails.add(orderdetail);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return orderdetails;
    }

    /**
     * 根据用户id查询用户所有订单的订单号、订单时间、订单状态
     * @return
     */
    public static List<Orderdetail> consigneeByIDorder(Integer user_id){
        List<Orderdetail> orderdetails = new ArrayList<>();
        String sql = "SELECT ordernumber,OrderSubmissionTime,state,receiverAddress FROM m_order WHERE ordernumber in (SELECT b.ordernumber from m_orderdetail as b GROUP BY b.ordernumber) AND ordernumber in (SELECT ordernumber from m_order WHERE user_id = ?);";
        log.debug(sql);
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,user_id);
            rs = ps.executeQuery();
            while (rs.next()){
                Orderdetail orderdetail = new Orderdetail();
                orderdetail.setOrdernumber(rs.getString(1));
                orderdetail.setOrderSubmissionTime(rs.getString(2));
                orderdetail.setState(rs.getString(3));
                orderdetail.setReceiverAddress(rs.getString(4));
                orderdetails.add(orderdetail);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return orderdetails;
    }

    /**
     * 根据我的订单传来的订单号来查询该订单号所包含的购物信息
     * 商品名称
     * 商品单价
     * 商品图片
     * 购买数量
     * 总价
     * @param ordernumber
     * @return
     */
    public List<Lndividual_orders> selectByOrdernumber(String ordernumber){
        List<Lndividual_orders> loList = new ArrayList<>();
        String sql = "SELECT a.consignee_name,a.consignee_price,a.consignee_picture,b.quantity FROM m_consignee a INNER JOIN m_orderdetail b ON a.consignee_id=b.consignee_id WHERE b.consignee_id in (SELECT consignee_id FROM m_orderdetail WHERE ordernumber=?) and b.ordernumber =?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,ordernumber);
            ps.setString(2,ordernumber);
            rs = ps.executeQuery();
            while (rs.next()){
                Lndividual_orders lo = new Lndividual_orders();
                lo.setConsignee_name(rs.getString(1));
                lo.setConsignee_price(rs.getInt(2));
                lo.setConsignee_picture(rs.getString(3));
                lo.setQuantity(rs.getInt(4));
                lo.setTotal_prices(rs.getInt(2)*rs.getInt(4));
                log.debug(String.valueOf(lo));
                loList.add(lo);
            }

        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return loList;
    }

    /**
     * 根据从我的订单传来的订单号来获取订单的信息
     * @return
     */
    public List<Order_browse> order_browseList(String ordernumber){
        List<Order_browse> browses = new ArrayList<>();
        List<Order_browse> orderdetailList_1 = selectByOrdernumberUser(ordernumber);
        List<Order_browse> orderdetailList_2 = selectByOrdernumberOrder(ordernumber);
        List<Order_browse> orderdetailList_3 = selectByOrdernumberprices(ordernumber);
        for (int i = 0; i < orderdetailList_1.size(); i++) {
            Order_browse browse = new Order_browse();
            browse.setConsigneeName(orderdetailList_1.get(i).getConsigneeName());
            browse.setPhone(orderdetailList_1.get(i).getPhone());
            browse.setReceiverAddress(orderdetailList_1.get(i).getReceiverAddress());
            browse.setOrdernumber(orderdetailList_2.get(i).getOrdernumber());
            browse.setOrderSubmissionTime(orderdetailList_2.get(i).getOrderSubmissionTime());
            browse.setPrices(orderdetailList_3.get(i).getPrices());
            log.debug(String.valueOf(browse));
            browses.add(browse);
        }
        return browses;
    }

    /**
     * 根据订单号来该订单用户的信息
     * 收件人姓名
     * 收件人电话
     * 收货地址
     * @param ordernumber
     * @return
     */
    public static List<Order_browse> selectByOrdernumberUser(String ordernumber){
        List<Order_browse> browses = new ArrayList<>();
        String sql = "SELECT consigneeName,phone,receiverAddress from m_order WHERE ordernumber = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,ordernumber);
            rs= ps.executeQuery();
            while (rs.next()){
                Order_browse browse = new Order_browse();
                browse.setConsigneeName(rs.getString(1));
                browse.setPhone(rs.getString(2));
                browse.setReceiverAddress(rs.getString(3));
                browses.add(browse);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }

        return browses;
    }

    /**
     * 根据订单号来查询该订单的订单号和订单时间
     * @param ordernumber
     * @return
     */
    public static List<Order_browse> selectByOrdernumberOrder(String ordernumber){
        List<Order_browse> browses = new ArrayList<>();
        String sql = "SELECT ordernumber,OrderSubmissionTime FROM m_order WHERE ordernumber in (SELECT b.ordernumber from m_orderdetail as b GROUP BY b.ordernumber) AND ordernumber in (SELECT ordernumber from m_order WHERE ordernumber = ?)";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,ordernumber);
            rs= ps.executeQuery();
            while (rs.next()){
                Order_browse browse = new Order_browse();
                browse.setOrdernumber(rs.getString(1));
                browse.setOrderSubmissionTime(rs.getString(2));
                browses.add(browse);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }

        return browses;
    }

    /**
     * 根据订单号来查询该订单的总价
     * @param ordernumber
     * @return
     */
    public static List<Order_browse> selectByOrdernumberprices(String ordernumber){
        List<Order_browse> browses = new ArrayList<>();
        String sql = "SELECT SUM(b.payable*b.quantity) FROM m_order a INNER JOIN m_orderdetail b ON a.ordernumber=b.ordernumber GROUP BY a.ordernumber HAVING a.ordernumber in (SELECT ordernumber from m_order WHERE ordernumber = ?)";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,ordernumber);
            rs= ps.executeQuery();
            while (rs.next()){
                Order_browse browse = new Order_browse();
                browse.setPrices(rs.getInt(1));
                browses.add(browse);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }

        return browses;
    }

    /**
     * 根据我的订单传来的订单号来查询该订单的用户的收件人姓名
     * @param ordernumber
     * @return
     */
    public String selelByconsigneeName(String ordernumber){
        String consigneeName = "";
        String sql = "SELECT a.consigneeName FROM m_user a INNER JOIN m_order b ON a.user_id=b.user_id WHERE b.ordernumber =?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,ordernumber);
            rs = ps.executeQuery();
            while (rs.next()){
                consigneeName = rs.getString(1);
            }

        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return consigneeName;
    }

    /**
     * 查询当前订单的状态
     * @param ordernumber
     * @return
     */
    public String selectByordernumber(String ordernumber){
        String state = "";
        String sql = "SELECT state from m_order WHERE ordernumber = ?";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,ordernumber);
            rs= ps.executeQuery();
            while (rs.next()){
                state = rs.getString(1);
            }
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return state;
    }

    /**
     * 判断当前用户的订单时候超时，如果超时订单失效
     * @param name
     * @return
     */
    public boolean update_order(String name){
        boolean favg = false;
        String sql = "UPDATE m_order SET state = '订单失效' WHERE ordernumber IN (SELECT id FROM (SELECT b.ordernumber id FROM m_order AS b LEFT JOIN m_user AS a ON a.user_id = b.user_id WHERE a.user_name =? AND b.state = '未付款' AND DATE_FORMAT(b.timelimit,'%Y-%m-%d %H:%i:%s') < DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')) AS res)";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,name);
            favg = ps.executeUpdate()>0;
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return favg;
    }

    /**
     * 判断所有用户的订单是否超时，如果超时订单失效
     * @return
     */
    public boolean updateByname_order(){
        boolean favg = false;
        String sql = "UPDATE m_order SET state = '订单失效' WHERE ordernumber IN (SELECT id FROM (SELECT b.ordernumber id FROM m_order AS b LEFT JOIN m_user AS a ON a.user_id = b.user_id WHERE a.user_name in (SELECT user_name FROM m_user) AND b.state = '未付款' AND DATE_FORMAT(b.timelimit,'%Y-%m-%d %H:%i:%s') < DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s')) AS res);";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try{
            conn = DBUtil.getConnection();
            ps = conn.prepareStatement(sql);
            favg = ps.executeUpdate()>0;
        }catch (Exception e){
            log.error(e.getMessage());
        }finally {
            DBUtil.closeAll(conn,ps,rs);
        }
        return favg;
    }

    public static String dataAdd(){
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Calendar c = Calendar.getInstance();
        c.add(Calendar.DAY_OF_MONTH, 1);
        return sf.format(c.getTime());
    }

    public static String nuw(){
        return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
    }
    public static void main(String[] args) {
//        List<Order> orders = consigneeList();
////        System.out.println(orders);
//        log.debug(nuw());
////        log.debug(dataAdd());
//        log.debug(String.valueOf(OrderDao.consigneeList()));
//        log.debug(String.valueOf(consigneeByIdList(32)));
//        log.debug(String.valueOf(selectByOrdernumber("8a90326fac024ba7a48375b1137b29e7")));
//        log.debug(String.valueOf(order_browseList("77059d5cdf7140f9b2c9827eee02003c")));
//        log.debug(String.valueOf(selectByOrdernumber("d93251dd2eed4cd7963bda3171b0e42f")));
    }
}
